Namespace persistent
    Public Class SmeStatuChange

        Public Shared Function GetContracts(ByVal custcode As String, ByVal statu As String) As DataTable
            Dim sql As String
            sql = " select x.*,ch.moddate prc_date from "
            sql += " (select v.ms,v.co_id, (select ch_status from curr_co_status where co_id=v.co_id) statu, "
            sql += " sfc.request_date req_date, sfc.old_status old_status,sfc.new_status new_status,sfc.request reqid, v.CustCode, "
            sql += " Fn_Msisdn_To_Iccid(v.ms) ICCID,"
            sql += " ("
            sql += " 	select des from mputmtab a where a.tmcode=v.tmcode "
            sql += " 	and a.VSCODE=(select max(b.vscode)from mputmtab b where a.tmcode=b.tmcode) "
            sql += " ) Tariff,"
            'sql += "v.dealer_id DealerCode, "
            sql += "NVL((select dealer_id from storage_medium where sm_serialnum = (select fn_msisdn_to_iccid(v.ms) from dual)), 9001) DealerCode, "
            'sql += "(select rs_desc from reasonstatus_all where rs_id = (select ch_reason from contract_history where co_id = v.co_id and ch_seqno = 2)) ActivationReason"
            sql += "(select rs_desc from reasonstatus_all where rs_id = (select ch_reason from contract_history where co_id = v.co_id and ch_seqno = (select max(ch_seqno) from  contract_history where co_id = v.co_id))) ActivationReason"
            sql += " from ( "
            sql += " select fn_coid_to_msisdn(co.co_id) ms,co.co_id, cu.dealer_id, cu.tmcode, cu.custcode  from customer_all cu, contract_all co  "
            sql += " where (cu.custcode like  '" & custcode & ".%' or cu.custcode='" & custcode & "' )  "
            sql += " and cu.customer_id=co.customer_id(+) "
            sql += " and cu.cslevel='40'"
            sql += " ) v ,COID_STATUS_CHANGE sfc "
            sql += " where v.co_id=sfc.coid(+) ) x,contract_history ch "
            sql += " where x.reqid=ch.request(+) "
            sql += " and ch.ch_pending(+) is null "
            If statu <> "t" Then sql += " and x.statu='" & statu & "' "
            sql += " order by 1 "

            Return myDBConn.myORATable(sql)

        End Function

        Public Shared Function GetFuPack(ByVal custcode As String) As DataTable
            Dim sql As String

            sql = " select sf.FU_PACK_ID,long_name,segment_ack from segment_sme ss,segment_fu sf , fu_pack fup, segment_ack sa where ss.segment_id=sf.segment_id "
            sql += " and ss.custcode='" & custcode & "' "
            sql += " and fup.FU_PACK_ID=sf.fu_pack_id "
            sql += " and ss.segment_id=sa.segment_id "

            Return myDBConn.myORATable(sql)

        End Function

        Public Shared Function customer_deactive() As DataTable
            Dim sql As String

            sql = " select co.customer_id,bp.VALUE from COID_STATUS_CHANGE sfc,contract_history ch, curr_co_status cr ,batch_req_param bp,contract_all co,customer_all cu"
            sql += " where sfc.request=ch.request(+) "
            sql += " and ch.ch_pending(+) is null "
            sql += " and ch.moddate is not null "
            sql += " and sfc.coid=cr.co_id(+)"
            sql += " and sfc.req_id=bp.req_id(+)"
            sql += " and sfc.coid=co.CO_ID(+)"
            sql += " and co.customer_id=cu.customer_id(+)"
            sql += " and bp.parameterno=2"
            sql += " and sfc.new_status='d' and cr.CH_STATUS='d'"
            sql += " and cu.cstype ='a' and cu.prgcode in ( select prgcode from  pricegroup_all where upper(prgname) like 'CORP%' or prgcode=29 )"

            Return myDBConn.myORATable(sql)

        End Function

        Public Shared Sub customer_control_on_table()

            Dim sql As String

            'sql = " select count(*) from ("
            'sql += " select x.*,ch.moddate prc_date from "
            'sql += " (select v.*, (select ch_status from curr_co_status where co_id=v.co_id) statu,"
            'sql += " sfc.request_date req_date, sfc.old_status old_status,sfc.new_status new_status,sfc.request reqid from ( "
            'sql += " select fn_coid_to_msisdn(co.co_id) ms,co.co_id  from customer_all cu, contract_all co  "
            'sql += " where (cu.custcode like  '" & custcode & ".%' or cu.custcode='" & custcode & "' )  "
            'sql += " and cu.customer_id=co.customer_id(+) "
            'sql += " and cu.cslevel='40' "
            'sql += " ) v ,COID_STATUS_CHANGE sfc "
            'sql += " where v.co_id=sfc.coid(+) ) x,contract_history ch "
            'sql += " where x.reqid=ch.request(+) "
            'sql += " and ch.ch_pending(+) is null "
            'sql += "  order by 1) log where log.prc_date is null "

            'If myDBConn.myORATable(sql).Rows(0)(0) = 0 Then

            '    sql = "insert into   COID_STATUS_CHANGE_LOG  "
            '    sql += " ( select * from COID_STATUS_CHANGE where custcode='" & custcode & "') "
            '    Dim l As Long = myDBConn.ORAExecute(sql)

            '    sql = " delete COID_STATUS_CHANGE where custcode='" & custcode & "' "
            '    Dim l1 As Long = myDBConn.ORAExecute(sql)

            'End If


            'sql = " insert into COID_STATUS_CHANGE_LOG ("
            'sql += " select x.custcode,x.co_id,x.request_date,null,x.request,x.old_status,x.new_status,x.req_id from "
            'sql += " (select v.*, (select ch_status from curr_co_status where co_id=v.co_id) statu,"
            'sql += " sfc.custcode,sfc.request_date request_date, sfc.old_status old_status,sfc.new_status new_status,sfc.request request,sfc.req_id from ( "
            'sql += " select co.co_id  from customer_all cu, contract_all co  "
            'sql += " where (cu.custcode like  '" & custcode & ".%' or cu.custcode='" & custcode & "' )  "
            'sql += " and cu.customer_id=co.customer_id(+) "
            'sql += " and cu.cslevel='40' "
            'sql += " ) v ,COID_STATUS_CHANGE sfc "
            'sql += " where v.co_id=sfc.coid(+) ) x,contract_history ch "
            'sql += " where x.request=ch.request(+) "
            'sql += " and ch.ch_pending(+) is null"
            'sql += " and ch.moddate is not null ) "
            sql = " insert into COID_STATUS_CHANGE_LOG ("
            sql += " select sfc.* from COID_STATUS_CHANGE sfc,contract_history ch, curr_co_status cr "
            sql += " where sfc.request=ch.request(+) "
            sql += " and ch.ch_pending(+) is null "
            sql += " and ch.moddate is not null "
            sql += " and sfc.coid=cr.co_id(+) "
            sql += " and sfc.new_status=cr.CH_STATUS )"

            Dim l As Long = myDBConn.ORAExecute(sql)

            sql = " delete COID_STATUS_CHANGE where (custcode,coid) in ("
            sql += " select sfc.custcode,sfc.coid from COID_STATUS_CHANGE sfc,contract_history ch, curr_co_status cr "
            sql += " where sfc.request=ch.request(+) "
            sql += " and ch.ch_pending(+) is null "
            sql += " and ch.moddate is not null "
            sql += " and sfc.coid=cr.co_id(+) "
            sql += " and sfc.new_status=cr.CH_STATUS ) "

            'sql = " delete COID_STATUS_CHANGE where (custcode,coid) in ("
            'sql += " select x.custcode,x.co_id from "
            'sql += " (select v.*, (select ch_status from curr_co_status where co_id=v.co_id) statu,"
            'sql += " sfc.custcode,sfc.request_date request_date, sfc.old_status old_status,sfc.new_status new_status,sfc.request request from ( "
            'sql += " select co.co_id  from customer_all cu, contract_all co  "
            'sql += " where (cu.custcode like  '" & custcode & ".%' or cu.custcode='" & custcode & "' )  "
            'sql += " and cu.customer_id=co.customer_id(+) "
            'sql += " and cu.cslevel='40' "
            'sql += " ) v ,COID_STATUS_CHANGE sfc "
            'sql += " where v.co_id=sfc.coid(+) ) x,contract_history ch "
            'sql += " where x.request=ch.request(+) "
            'sql += " and ch.ch_pending(+) is null"
            'sql += " and ch.moddate is not null ) "

            Dim l1 As Long = myDBConn.ORAExecute(sql)


        End Sub


        Public Shared Function insert_statu_change_request(ByVal custcode As String, ByVal co_id As String, ByVal statu_old As String, ByVal statu_new As String, ByVal req_id As String) As Long
            Dim strSQL As String

            strSQL = " insert into COID_STATUS_CHANGE values ('" & custcode & "','" & co_id & "',sysdate,null,null,'" & statu_old & "','" & statu_new & "','" & req_id & "')"

            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function update_Status_request(ByVal custcode As String, ByVal co_id As String) As Long
            Dim strSQL As String

            strSQL = " select request from contract_history c where co_id=" & co_id & " and ch_seqno =(select max(ch_seqno) from contract_history where co_id=c.co_id)"
            Dim req As String = myDBConn.myORATable(strSQL).Rows(0)(0)

            strSQL = "update COID_STATUS_CHANGE set request=" & req & " where custcode='" & custcode
            strSQL += "' and coid=" & co_id & " and request is null and process_date is null "

            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function selectCustType(ByVal user As String) As DataTable
            Dim sql As String

            sql = "  select userid from login_user where userid='" & user & "' and  div_id = 4 "

            Return myDBConn.myORATable(sql)

        End Function


        Public Shared Function GetRequestedContract(ByVal custcode As String) As DataTable
            Dim sql As String

            'sql = " select coid,new_status from COID_STATUS_CHANGE where custcode='" & custcode & "' and request_date is not null and process_date is null and request is null "

            sql = " select coid,new_status,co.customer_id from COID_STATUS_CHANGE cs,contract_all co  where cs.custcode='" & custcode & "' and"
            sql += "  cs.coid = co.co_id And request_date Is Not null And process_date Is null And request Is null"

            Return myDBConn.myORATable(sql)

        End Function


        Public Shared Function checkDeletedCustomer() As DataTable

            Dim sql As String

            sql = " select x.customer_id, br.value, (select count(*) from contract_all where customer_id=x.customer_id) real_tot , count(*) cnt from "
            sql += " (select v.*, (select ch_status from curr_co_status where co_id=v.co_id) statu from "
            sql += " ( select coid co_id,request,cs.custcode,co.customer_id,req_id  from COID_STATUS_CHANGE cs,contract_all co,customer_all cu "
            sql += " where cs.coid=co.co_id and co.customer_id=cu.customer_id and (cu.prgcode in ( select prgcode from pricegroup_all where prgname like '%SME%' and prgcode!=29)) and cu.cstype!='d' ) v "
            sql += " ) x,contract_history ch, batch_req_param br "
            sql += " where x.request=ch.request(+) "
            sql += " and ch.ch_pending(+) is null"
            sql += " and ch.moddate is not null "
            sql += " and x.statu='d'"
            sql += " and x.req_id=br.req_id(+)"
            sql += " and br.parameterno=2"
            sql += " group by x.customer_id,br.value"

            Return myDBConn.myORATable(sql)


        End Function


        Public Shared Function CheckStatus(ByVal co_id As String, ByVal new_status As String) As DataTable
            Dim sql As String

            sql = "  select co_id from curr_co_status where co_id=" & co_id & " and ch_status='" & new_status & "' "

            Return myDBConn.myORATable(sql)

        End Function

        Public Shared Function check_contract(ByVal custcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode like '" & custcode & "%' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and cur.ch_status in ('a','s') "

            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function msisdnTOcustcode(ByVal msisdn As String) As DataTable
            Dim sql As String
            Dim cst As String

            sql = "select nvl(customer_id_high,0),customer_id from customer_all where customer_id =(select fn_msisdn_to_custid('" & msisdn & "') from dual) "
            If myDBConn.myORATable(sql).Rows(0)(0) > 0 Then
                cst = myDBConn.myORATable(sql).Rows(0)(0)
            Else
                cst = myDBConn.myORATable(sql).Rows(0)(1)
            End If
            sql = " select custcode from customer_all a where customer_id = " & cst
            'sql = " select custcode from customer_all a where customer_id = (select fn_msisdn_to_custid('" & msisdn & "') from dual) "

            'sql = " select custcode,cslevel,(select custcode from customer_all where customer_id=a.customer_id_high ) from customer_all a where customer_id in "
            'sql = sql + " (select customer_id_high from customer_all where customer_id in "
            'sql = sql + " (select customer_id_high from customer_all where customer_id= "
            'sql = sql + " (select fn_msisdn_to_custid('" & msisdn & "') from dual) "
            'sql = sql + " ) and (cslevel='20' or cslevel='30') "
            'sql = sql + " ) and (cslevel='10' or cslevel='20') "
            Return myDBConn.myORATable(sql)
        End Function

        Public Shared Function getcustcode(ByVal msisdn As String) As String
            Dim strSQL As String
            strSQL = "Select DECODE(cu2.cslevel, 30, CU3.CUSTCODE, CU2.CUSTCODE) "
            strSQL = strSQL + "FROM contract_all  A, customer_all cu1, customer_all cu2, customer_all cu3 "
            strSQL = strSQL + "WHERE(a.CUSTOMER_ID = CU1.CUSTOMER_ID) "
            strSQL = strSQL + "AND   cu3.customer_id=cu2.customer_id_high "
            strSQL = strSQL + "AND   cu2.customer_id=cu1.customer_id_high "
            strSQL = strSQL + "AND   A.co_id = (select max(B.co_id) "
            strSQL = strSQL + "             from contr_services_cap  B, eu_bill.view_directory_number  C "
            strSQL = strSQL + "               where C.dn_num='" & msisdn & "' "
            strSQL = strSQL + "                and B.dn_id=C.dn_id"
            strSQL = strSQL + "               and B.main_dirnum='X')"
            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function getCustomerCountList(ByVal custcode As String) As DataTable
            Dim strSQL As String

            strSQL = " select b1.custcode,a1.cnt from "
            strSQL += " ("
            strSQL += " select customer_id_high ch,count(*) cnt from customer_all a where custcode like '" & custcode & "%'"
            strSQL += " and a.cstype in ('a','s') and a.csdeactivated is null and cslevel =40 "
            strSQL += " group by customer_id_high"
            strSQL += " ) a1,customer_all b1"
            strSQL += " where a1.ch=b1.customer_id(+) "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function getHolding(ByVal custcode As String) As String
            Dim strSQL As String

            strSQL = " select '10',a.custcode,'',b.ccname,'-','-','-','-','a',1 from  customer_all a,ccontact_all b"
            strSQL += " where a.custcode = '" & custcode & "' "
            strSQL += " and a.cstype in ('a','s') and a.csdeactivated is null "
            strSQL += " and a.customer_id=b.customer_id(+) "
            strSQL += " and b.ccbill(+)='X' "

            Return strSQL
        End Function

        Public Shared Function getCompany(ByVal custcode As String) As String
            Dim strSQL As String

            strSQL = " select '20',a.custcode,'',b.ccname,'-','-','-','-','a',1 from  customer_all a,ccontact_all b"
            strSQL += " where a.custcode = '" & custcode & "' "
            strSQL += " and a.cstype in ('a','s') and a.csdeactivated is null "
            strSQL += " and a.customer_id=b.customer_id(+) "
            strSQL += " and b.ccbill(+)='X' "

            Return strSQL
        End Function


        Public Shared Function getContractList(ByVal statement As String) As DataTable
            Return myDBConn.myORATable(statement)
        End Function


        Public Shared Function GetSegment(ByVal custCode As String) As String
            Dim query As String = "SELECT  SA.SEGMENT_ACK " & _
                                "FROM BILLINTRA.SEGMENT_SME SS, SEGMENT_ACK SA, SYSADM.CUSTOMER_ALL CUA, SYSADM.CCONTACT_ALL COA " & _
                                "WHERE SS.SEGMENT_ID = SA.SEGMENT_ID AND  " & _
                                "CUA.CUSTCODE = SS.CUSTCODE AND " & _
                                "COA.CUSTOMER_ID = CUA.CUSTOMER_ID AND " & _
                                "CUA.CUSTCODE = '" + custCode + "'"

            Dim result As Object = myDBConn.ORAScalar(query)
            If Not result Is Nothing AndAlso Not IsDBNull(result) Then
                Return CType(result, String)
            Else
                Return "Belirtilmedi!"
            End If
        End Function

    End Class
End Namespace
